<?php if ( !defined('BASEPATH')) exit('No direct script access allowed');

class Arearscount extends CI_Controller {

	public function __construct(){
		parent::__construct();
		$this->load->model('wus');
		$this->load->model($this->session->userdata('formodel'));
		$this->load->library('variable');
		$this->load->library('PHPexcel');

	}

	public function index(){
	  $this->load->view('layouts/header',$this->variable->load_data($this->session->userdata('title')));
	  $this->load->view($this->session->userdata('formain'),array('zones' => $this->arearscount_model->get_zones(),'zone1' => $this->arearscount_model->getzoning('ZONE1')));
	  $this->load->view('layouts/footer');
	 }

	 public function add_form(){
		$this->load->view($this->session->userdata('foradd'));
	}

	public function edit_form($id){
		$this->load->view($this->session->userdata('foredit'));
	}

	public function view_form($id){
		$this->load->view($this->session->userdata('forview'));
	}

	public function get_status() {
		if($this->input->post('disconnection') == "DIS"){
			echo json_encode($this->arearscount_model->get_arrears_dis($this->input->post('areas')));
		}else if($this->input->post('disconnection') == "DISC"){
			echo json_encode($this->arearscount_model->get_arrears_disc($this->input->post('areas')));
		}else{
			echo json_encode($this->arearscount_model->get_arrears($this->input->post('one'),$this->input->post('two'),$this->input->post('areas')));
		}
	}

	public function get_pend(){
		$sure = $this->arearscount_model->get_pnd($_GET['id']);
		$this->load->view($this->session->userdata('foradd'),array(
			'title' => 'Pending List',
			'datas'	=> $sure
			));
		//echo json_encode($this->billprogress_model->get_pnd($_GET['date'],$_GET['zone']));
		//var_dump($this->billprogress_model->get_pnd($_GET['date'],$_GET['zone']));
	}

	public function getOneMonth($date)
	{
		$dates = date_create($date);
		date_add($dates, date_interval_create_from_date_string('-1 months'));
		return date_format($dates, 'M d, Y');
	}

	public function formatDate($date)
	{
		$dates = date_create($date);
		return date_format($dates, 'M d, Y');
	}

	public function getDates()
	{
		echo json_encode($this->arearscount_model->get_pnd($this->input->post('account_number')));
	}

	public function getCount($id)
	{
		$value = $this->arearscount_model->get_pnd($id);
		$total = 0;
		$table = '';
		$pad = '5px';
		foreach ($value as $k) {
			$table .= "<tr><td style='padding:".$pad." ;'>".$this->formatDate($k->invoice_date)."</td><td style='padding:".$pad." ;'>".$this->formatDate($k->due_date)."</td><td style='padding:".$pad." ;'>".$this->getOneMonth($k->invoice_date)."</td><td align='right' style='padding:".$pad." ;'>".number_format($k->balance, 2, ".", "," )."</td></tr>";
			$total = $total + $k->balance;
		}

		return "<table style='width: 100%;''>".$table."<tr><td style='border-top: 1px solid #000;' colspan='4' align='right'>".number_format($total, 2, ".", "," )."</td></tr></table>";
	}

	public function gen_excel() {
		$q;
		if($_GET['disconnection'] == "DIS"){
			$q = $this->arearscount_model->get_arrears_dis($_GET['areas']);
		}else{
			$q = $this->arearscount_model->get_arrears($_GET['one'],$_GET['two'],$_GET['areas']);
		}
		//activate worksheet number 1
		$excel = new PHPExcel();
		$excel->setActiveSheetIndex(0);
		//name the worksheet
		$excel->getActiveSheet()->setTitle('Collection Details Report');
		//set cell A1 content with some text
		$excel->getActiveSheet()->setCellValue('A1', 'Arrears Count');
		$excel->getActiveSheet()->setCellValue('A3', 'Area');
		$excel->getActiveSheet()->setCellValue('B3', 'Account Number');
		$excel->getActiveSheet()->setCellValue('C3', 'Account Name');
		$excel->getActiveSheet()->setCellValue('D3', 'Arrears Account');
		$excel->getActiveSheet()->setCellValue('E3', 'Date');
		$excel->getActiveSheet()->setCellValue('F3', 'Due Date');
		$excel->getActiveSheet()->setCellValue('G3', 'Month');
		$excel->getActiveSheet()->setCellValue('H3', 'Open Balance');
		$cellnum = 4;
		//var_dump($q);

		foreach ($q as $key) {
			$total = 0;
			$x = $cellnum;
			$excel->getActiveSheet()->setCellValue('A' . $cellnum, $key->area);
			$excel->getActiveSheet()->setCellValue('B' . $cellnum, $key->num);
			$excel->getActiveSheet()->setCellValue('C' . $cellnum, $key->name);
			$excel->getActiveSheet()->setCellValue('D' . $cellnum, $key->a_count);
				foreach ($this->arearscount_model->get_pnd($key->num) as $k) {
					$excel->getActiveSheet()->setCellValue('E' . $cellnum, $this->formatDate($k->invoice_date));
					$excel->getActiveSheet()->setCellValue('F' . $cellnum, $this->formatDate($k->due_date));
					$excel->getActiveSheet()->setCellValue('G' . $cellnum, $this->getOneMonth($k->invoice_date));
					$excel->getActiveSheet()->setCellValue('H' . $cellnum, $k->balance);
					$total = $total + $k->balance;
					$cellnum++;
				}
			$excel->getActiveSheet()->setCellValue('H' .$cellnum, $total);
			//$cellnum 
			++$cellnum;
		}

		$value = $this->arearscount_model->get_pnd('110234');

		$excel->getActiveSheet()->getStyle("A3:H3")->getFont()->setBold(true);
		$excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
		$excel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
		$excel->getActiveSheet()->getColumnDimension('C')->setWidth(40);
		$excel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$excel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$excel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		$excel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
		$excel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
		//change the font size
		$excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(12);
		//make the font become bold
		$excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
		//merge cell A1 until D1
		$excel->getActiveSheet()->mergeCells('A1:D1');
		//set aligment to center for that merged cell (A1 to D1)
		$excel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$excel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		//$excel->getActiveSheet()->getStyle("A2:F2")->getFont()->getColor()->setRGB('FFFFFF');

		$excel->getActiveSheet()->setShowGridlines(false);

		$filename = 'Arrears_Count_Report ' . date('Ymd_his') . '.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
		$objWriter->save('php://output');
		//force user to download the Excel file without writing it to server's HD
		//$objWriter->save("assets/tmp/" . $filename);
		//$this->index();
		// $data = ob_get_contents();
		//$tester =  $objWriter->save($filename);
		//force_download($filename,$objWriter->save('php://output')));
	}

	public function gen_pdf() {	
		$q;
		if($_GET['disconnection'] == "DIS"){
			$q = $this->arearscount_model->get_arrears_dis($_GET['areas']);
		}else{
			$q = $this->arearscount_model->get_arrears($_GET['one'],$_GET['two'],$_GET['areas']);
		}
		//var_dump($result);
		$table_data = '';
		//<td style='border-bottom:1px solid; text-align: center;width=8%; padding: 5px;'>Year</td>
		//<td style="padding-right: 5px; padding-left: 5px; padding-top: 5px;" align="right">' . $key->year . '</td>
		foreach ($q as $key) {
			$table_data .= '<tr><td style="padding-right: 5px; padding-left: 5px; padding-top: 5px;">' . $key->area . '</td>
								<td style="padding-right: 5px; padding-left: 5px; padding-top: 5px;" align="right">' . $key->num . ' </td>
								<td style="padding-right: 5px; padding-left: 5px; padding-top: 5px;" align="left">' . $key->name . '</td>
								<td style="padding-right: 5px; padding-left: 5px; padding-top: 5px;" align="right">' . $key->a_count . '</td>
								<td style="padding-right: 5px; padding-left: 5px; padding-top: 5px;" align="right" colspan="4">' . $this->getCount($key->num) . '</td></tr>';
		}
		$content = "
		<page>
        	<table style='width: 100%; padding-top:-10px;'>
	            <tr>
			        <td style='text-align: center;width=33%;'><hr style='height: .3mm; background: #000; '></td>
			        <td style='text-align: center;width=33%; padding-top:-5px;'>
						<h4>Arrears Count From " . $_GET['one'] . " to " . $_GET['two'] . "</h4>
					</td>
					<td style='text-align: center;width=33%;'><hr style='height: .3mm; background: #000; '>     </td>
				</tr>
			</table><br><br>
			<table cellspacing='0' style='width: 100%; position: relative;'>
				<tr>
					<td style='border-bottom:1px solid; text-align: center;width=10%; padding: 5px;'>Area</td>
					<td style='border-bottom:1px solid; text-align: center;width=10%; padding: 5px;'>Account Number</td>
					<td style='border-bottom:1px solid; text-align: center;width=25%; padding: 5px;'>Account Name</td>
					<td style='border-bottom:1px solid; text-align: center;width=10%; padding: 5px;'>Arrears Count</td>
					<td style='border-bottom:1px solid; text-align: center;width=5%; padding: 5px;'>Date</td>
					<td style='border-bottom:1px solid; text-align: center;width=5%; padding: 5px;'>Due Date</td>
					<td style='border-bottom:1px solid; text-align: center;width=5%; padding: 5px;'>Month</td>
					<td style='border-bottom:1px solid; text-align: center;width=5%; padding: 5px;'>Open Balance</td>
				</tr>" .
			$table_data
			. "</table>
		</page>
		";
		require_once dirname(__FILE__).'\..\..\..\assets\html2pdf\html2pdf.class.php';
		$html2pdf = new HTML2PDF('P', 'A4', 'en');
		$html2pdf->pdf->SetTitle('Arrears Count Report');
		$html2pdf->WriteHTML($content);
		$html2pdf->Output('Arrears_Count' . date("dmY_His") . '.pdf', 'D');
	}

}